import time
# from tools.db_demo import db
from WoniuBook.DB import db
username=''

# 用户注册
def reg():
    users=db.query(sql='select * from user')
    # print(users)
    name = input('请输入用户名[注册]：')
    password = input('请输入您的密码[注册]：')
    nickname = input('请输入您的姓名[注册]：')
    phone = input('请输入您的手机号[注册]：')
    for i in users:
        if i['username'] == name:
            print('您输入的用户名已被注册，请重新输入！')
            reg()
        elif i['phone'] == phone:
            print('您输入的手机号已被注册，请重新输入！')
            reg()
    else:
        print('恭喜您注册成功')
        sql = f"insert user (username,password,nickname,phone) values('{name}','{password}','{nickname}','{phone}')"
        db.update(sql)
        time.sleep(1)
        star_menu()

# 用户登录
def login():
    global username
    names = db.query(sql='select username from user')
    users = db.query(sql='select username,password,status from user')
    b = [i['username'] for i in names]
    print(b)
    print(users)
    a=0

    while True:
        username = input('请输入用户名[登录]：')
        password = input('请输入密码[登录]：')
        if username not in b:
            print('账号或密码错误，请重新输入.')
            login()
        for j in users:
            if username == j['username'] == 'admin' and password == j['password']:
                # print('登陆成功')
                admin_menu()

            elif username == j['username'] != 'admin' and password == j['password'] and j['status'] == '0':
                # print('登陆成功')
                user_menu()

            elif username == j['username'] != 'admin' and j['status'] == '-1':
                print('您的账户已锁定，请联系管理员解锁')
                star_menu()

        else:
            a += 1
            if a < 3:
                print('密码错误.')
                print(f'您还有{3 - a}次机会')

            else:
                print('密码错误超过三次，用户已锁定，请联系管理员解锁.')
                sql = f"update user set status='-1' where username='{username}'"
                db.update(sql)
                star_menu()


# 管理员菜单
def admin_menu():
    print("请选择操作菜单".center(98, "*"))
    print("1.查询图书 2.查看借阅信息 3.添加图书 4.删除图书 5.解除账号锁定 6.修改密码 7.返回 8.退出".center(78, "*"))
    option = input("请输入你的操作选项：")
    if option == "1":
        query_books()  # 查询图书
    elif option == "2":
        query_records()  # 查看借阅信息
    elif option == "3":
        add_book()  # 添加图书
    elif option == "4":
        del_book()  # 删除图书
    elif option == "5":
        unlock()  # 解锁
    elif option == "6":
        reset_pwd()  # 修改密码
    elif option == "7":
        star_menu()  # 返回
    elif option == "8":
        exit("再见，欢迎下次光临。")  # 退出
    else:
        print("你输入的选项有误，请重新选择！")
        admin_menu()

# 普通用户菜单
def user_menu():
    print("请选择操作菜单".center(98, "*"))
    print("1.查询图书 2.借书 3.归还图书 4.返回 5.退出".center(92, "*"))
    option = input("请输入你的操作选项：")
    if option == "1":
        query_books()  # 查询图书
    elif option == "2":
        borrow_book()  # 借书
    elif option == "3":
        return_book()  # 归还图书
    elif option == "4":
        star_menu()  # 返回
    elif option == "5":
        exit("再见，欢迎下次光临。")  # 退出
    else:
        print("你输入的选项有误，请重新选择！")
        user_menu()

# 查询图书
def query_books():
    print('请选择您的查询方式：1.查看所有图书       2.根据书名查看')
    option = input('请输入您的选择：')
    def queryall():  # 查看所有
        if username == 'admin':
            sql = 'select * from book'
            print(db.query(sql))
            time.sleep(1)
            admin_menu()
        elif username != 'admin':
            sql1 = "select name,author,count from book where status!='-1' and count !='0'"
            print(db.query(sql1))
            time.sleep(1)
            user_menu()

    def query_bookname():  # 书名查看
        b_name = input('请输入您要查看的书名：')
        if username == 'admin':
            sy=db.query(sql=f'select * from book where name="{b_name}"')
            if type(sy) == list:
                print(sy)
                admin_menu()
            else:
                print('您输入的图书不存在，请重新输入')
                query_bookname()
        elif username != 'admin':
            sy1=db.query(sql=f"select name,author,count from book where name='{b_name}' and status != '-1'")
            if type(sy1) == list:
                print(sy1)
                user_menu()
            else:
                print('您输入的图书不存在，请重新输入')
                query_bookname()

    if option == "1":  # 查询所有
        queryall()
    elif option == "2":  # 根据书名查询
        query_bookname()
    else:
        print("你输入的选项有误，请重新选择！")
        query_books()

# 查看借阅信息
def query_records():
    print('请选择查看方式:1.查看所有        2.根据姓名查看        3.根据手机号查看')
    option = input('请输入您的选项：')
    if option == "1":
        sql = 'select * from records'
        print(db.query(sql))
        time.sleep(1)

    elif option == "2":
        name = input('请输入借阅人：')
        sql = f"select * from user left join records on user.id=records.user_id where nickname='{name}'"
        print(db.query(sql))
        time.sleep(1)

    elif option == "3":
        phone = input('请输入手机号：')
        sql = f"select * from user left join records on user.id=records.user_id where phone='{phone}'"
        print(db.query(sql))
        time.sleep(1)
    else:
        print("你输入的选项有误，请重新选择！")
        query_records()
    admin_menu()

# 添加图书
def add_book():
    book_name = input('书名：')
    author = input('作者：')
    count = input('数量：')
    sql = f"insert book (name,author,count) values ('{book_name}','{author}',{count})"
    db.update(sql)
    print('添加图书成功!')
    time.sleep(1)
    admin_menu()

# 删除图书
def del_book():
    book_name = input('请输入要删除的图书:')
    sql = f"update book set status='-1' where name='{book_name}'"
    db.update(sql)
    print('删除图书成功！')
    time.sleep(1)
    admin_menu()

# 解锁
def unlock():
    print('1.查看所有锁定用户   2.解锁用户')
    option = input("请输入你的操作选项：")
    if option == "1":
        sql = "select * from user where status='-1'"
        print(db.query(sql))
    elif option == "2":
        user_name = input('请输入需要解锁的用户名：')
        sql1 = f"update user set status='0' where username='{user_name}'"
        db.update(sql1)
        print('用户解锁成功！')
    else:
        print("指令错误，重新选择。")
        unlock()
    admin_menu()

# 修改密码
def reset_pwd():
    pwd = input('请输入新密码：')
    sql = f"update user set password={pwd} where username='admin'"
    db.update(sql)
    time.sleep(1)
    admin_menu()

# 借书
def borrow_book():
    a = db.query(f"select id from user where username='{username}'")  # 获取当前用户id
    u_id = a[0]['id']
    js = input('请输入您要借的书：')
    shijian = time.strftime('%Y-%m-%d %H:%M:%S')  # 借书时间
    bid = db.query(f"select id from book where name='{js}'")
    if type(bid) == list:
        pass
    else:
        print('您输入的图书不存在，请重新输入')
        borrow_book()

    b_id = bid[0]['id']
    books = db.query("select id,name,author,count from book where count>=1 and `status`='0'")  # 有库存且允许借阅的书
    booked = db.query(
        f"select bookid,name from user inner join records on user.id=user_id inner join book on book.id = records.bookid where return_time is null and username='{username}'")  # 借了没还
    sql2 = f"insert records (user_id,bookid,borrow_time) values('{u_id}','{b_id}','{shijian}')"
    sql3 = f"update book set count=count-1 where id='{b_id}'"
    a = [i['name'] for i in books]  # 有库存且允许借阅的书
    b = [i['name'] for i in booked]  # 借了没还的书
    if js in a and js not in b:
        db.update(sql2)
        db.update(sql3)
        time.sleep(1)
        print('借书成功！')
    elif js in b:
        print('请还书后再借书！')
    else:
        print('本书不能借出！')
    user_menu()

# 还书
def return_book():
    # book_name=db.query(f'select * from ')
    a = input('请输入还书的书名：')
    return_time = time.strftime('%Y-%m-%d %H:%M:%S')
    sql = f"update book set count=count+1 where name='{a}'"
    sql2 = f"select id from book where name='{a}'"
    b = db.query(sql2)  # 查id
    c = b[0]['id']  # id变量
    sql1 = f"update records set return_time='{return_time}' where bookid='{c}'"
    db.update(sql)  # 执行还书书库存加1
    db.update(sql1)  # 添加还书时间
    print('还书成功!')
    time.sleep(1)
    user_menu()

# 开始菜单
def star_menu():
    """
    开始菜单
    :return: None
    """
    print("欢迎来到WoniuBook".center(100, "*"))
    print("请选择操作菜单".center(98, "*"))
    print("1.注册  2.登录  3.退出".center(98, "*"))
    option = input("请输入你的操作选项：")
    if option == "1":
        reg()  # 注册功能
    elif option == "2":
        login()  # 登录功能
    elif option == "3":
        exit("谢谢,欢迎下次光临。")  # 结束程序运行，括号中的内容是，结束时的提示符。
    else:
        print("指令错误，重新选择。")
        star_menu()
star_menu()






